/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50639
 Source Host           : localhost:3306
 Source Schema         : api-gateway

 Target Server Type    : MySQL
 Target Server Version : 50639
 File Encoding         : 65001

 Date: 07/08/2023 10:11:12
*/

DROP schema IF EXISTS `simple_gateway`;
create schema `simple_gateway`;

use simple_gateway;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for http_statement === test table
-- ----------------------------
DROP TABLE IF EXISTS `http_statement`;
CREATE TABLE `http_statement`
(
    `id`                bigint(21)                    NOT NULL AUTO_INCREMENT comment '主键id',
    `application`       varchar(128) COLLATE utf8_bin NOT NULL COMMENT '应用名称',
    `interface_name`    varchar(256) COLLATE utf8_bin NOT NULL COMMENT '服务接口；RPC、其他',
    `method_name`       varchar(128) COLLATE utf8_bin NOT NULL COMMENT ' 服务方法；RPC#method',
    `parameter_type`    varchar(256) COLLATE utf8_bin NOT NULL COMMENT '参数类型(RPC 限定单参数注册)；new String[]{"java.lang.String"}}',
    `uri`               varchar(128) COLLATE utf8_bin NOT NULL COMMENT '网关接口',
    `http_command_type` varchar(32) COLLATE utf8_bin  NOT NULL COMMENT '接口类型；GET、POST、PUT、DELETE',
    `auth`              int(4)                        NOT NULL DEFAULT '0' COMMENT 'true = 1是、false = 0否',
    `create_time`       datetime                               DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`       datetime                               DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `is_deleted`        int(1)                                 DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 3
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment 'http语句表';

-- ----------------------------
-- Records of http_statement === test table
-- ----------------------------
BEGIN;
INSERT INTO simple_gateway.http_statement (id, application, interface_name, method_name, parameter_type, uri,
                                           http_command_type, auth, create_time, update_time)
VALUES (1, 'simple-gateway-test-provider', 'com.simple.gateway.test.provider.rpc.IActivityBooth', 'sayHi',
        'java.lang.String', '/test/sayHi', 'GET', 0, now(), now());
INSERT INTO simple_gateway.http_statement (id, application, interface_name, method_name, parameter_type, uri,
                                           http_command_type, auth, create_time, update_time)
VALUES (2, 'simple-gateway-test-provider', 'com.simple.gateway.test.provider.rpc.IActivityBooth', 'insert',
        'com.simple.gateway.test.provider.rpc.dto.XReq', '/test/insert', 'POST', 1, now(), now());
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- Table structure for gateway_server
-- ----------------------------
DROP TABLE IF EXISTS `gateway_server`;
CREATE TABLE `gateway_server`
(
    `id`          bigint(21) NOT NULL AUTO_INCREMENT comment '主键id',
    `group_id`    varchar(32) COLLATE utf8_bin  DEFAULT NULL COMMENT '分组标识',
    `group_name`  varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '分组名称',
    `extension`   text(4000) COLLATE utf8_bin   DEFAULT null comment '扩展字段，建议存json格式',
    `create_time` datetime                      DEFAULT NULL COMMENT '创建时间',
    `update_time` datetime                      DEFAULT NULL COMMENT '更新时间',
    `is_deleted`  int(1)                        DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment '网关服务表';

-- ----------------------------
-- Records of gateway_server
-- ----------------------------
BEGIN;
INSERT INTO simple_gateway.gateway_server (id, group_id, group_name, create_time, update_time, is_deleted)
VALUES (1, '10001', '测试组', now(), now(), 0);
COMMIT;

-- ----------------------------
-- Table structure for gateway_server_detail
-- ----------------------------
DROP TABLE IF EXISTS `gateway_server_detail`;
CREATE TABLE `gateway_server_detail`
(
    `id`              bigint(21) NOT NULL AUTO_INCREMENT comment '主键id',
    `group_id`        varchar(32) COLLATE utf8_bin  DEFAULT NULL COMMENT '分组标识',
    `gateway_id`      varchar(32) COLLATE utf8_bin  DEFAULT NULL COMMENT '网关标识',
    `gateway_name`    varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '网关名称',
    `gateway_address` varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '网关地址：127.0.0.1',
    `status`          varchar(4) COLLATE utf8_bin   DEFAULT NULL COMMENT '服务状态：0不可用、1可使用',
    `extension`       text(4000) COLLATE utf8_bin   DEFAULT null comment '扩展字段，建议存json格式',
    `create_time`     datetime                      DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`     datetime                      DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `is_deleted`      int(1)                        DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_gateway` (`gateway_id`, `gateway_address`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment '网关详情表';

-- ----------------------------
-- Records of gateway_server_detail
-- ----------------------------
BEGIN;
INSERT INTO simple_gateway.gateway_server_detail (id, group_id, gateway_id, gateway_name, gateway_address, status,
                                                  create_time, update_time, is_deleted)
VALUES (1, '10001', 'api-gateway-g1', '电商支付网关', '127.0.0.196', 1, now(), now(), 0);
INSERT INTO simple_gateway.gateway_server_detail (id, group_id, gateway_id, gateway_name, gateway_address, status,
                                                  create_time, update_time, is_deleted)
VALUES (2, '10001', 'api-gateway-g2', '电商支付网关', '127.0.0.197', 1, now(), now(), 0);
INSERT INTO simple_gateway.gateway_server_detail (id, group_id, gateway_id, gateway_name, gateway_address, status,
                                                  create_time, update_time, is_deleted)
VALUES (3, '10001', 'api-gateway-g3', '电商配送网关', '127.0.0.198', 1, now(), now(), 0);
COMMIT;

-- ----------------------------
-- Table structure for gateway_distribution
-- ----------------------------
DROP TABLE IF EXISTS `gateway_distribution`;
CREATE TABLE `gateway_distribution`
(
    `id`          bigint(21) NOT NULL AUTO_INCREMENT comment '主键id',
    `group_id`    varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '分组标识',
    `gateway_id`  varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '网关标识',
    `system_id`   varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '系统标识',
    `system_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '系统名称',
    `extension`   text(4000) COLLATE utf8_bin   DEFAULT null comment '扩展字段，建议存json格式',
    `create_time` datetime                      DEFAULT NULL COMMENT '创建时间',
    `update_time` datetime                      DEFAULT NULL COMMENT '更新时间',
    `is_deleted`  int(1)                        DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment '网关分配表';

-- ----------------------------
-- Records of gateway_distribution
-- ----------------------------
BEGIN;
INSERT INTO simple_gateway.gateway_distribution (id, group_id, gateway_id, system_id, system_name, create_time,
                                                 update_time, is_deleted)
VALUES (1, '10001', 'api-gateway-g4', 'simple-gateway-test-provider', '网关测试系统', now(), now(), 0);
INSERT INTO simple_gateway.gateway_distribution (id, group_id, gateway_id, system_id, system_name, create_time,
                                                 update_time, is_deleted)
VALUES (2, '10001', 'api-gateway-g4', 'db-mysql-local-test', 'mysql测试', now(), now(), 0);

COMMIT;


-- ----------------------------
-- Table structure for application_system
-- ----------------------------
DROP TABLE IF EXISTS `application_system`;
CREATE TABLE `application_system`
(
    `id`              bigint(21) NOT NULL AUTO_INCREMENT comment '主键id',
    `system_id`       varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '系统标识',
    `system_name`     varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '系统名称',
    `system_type`     varchar(32) COLLATE utf8_bin  DEFAULT NULL COMMENT '系统类型；RPC、HTTP、DB',
    `system_registry` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '注册中心、数据源连接',
    `extension`       text(4000) COLLATE utf8_bin   DEFAULT null comment '扩展字段，建议存json格式',
    `create_time`     datetime                      DEFAULT NULL COMMENT '创建时间',
    `update_time`     datetime                      DEFAULT NULL COMMENT '更新时间',
    `is_deleted`      int(1)                        DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_systemId` (`system_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment '应用系统表';

-- ----------------------------
-- Records of application_system
-- ----------------------------
BEGIN;
INSERT INTO simple_gateway.application_system (id, system_id, system_name, system_type, system_registry, create_time,
                                               update_time, is_deleted)
VALUES (1, 'simple-gateway-test-provider', '网关测试系统', 'RPC_DUBBO', 'zookeeper://127.0.0.1:2181', now(), now(), 0);
INSERT INTO simple_gateway.application_system (id, system_id, system_name, system_type, system_registry, extension,
                                               create_time,
                                               update_time, is_deleted)
VALUES (2, 'db-mysql-local-test', '本地测试库', 'DB_MYSQL', 'jdbc:mysql://127.0.0.1:3306', ' {
    "dbName": "test",
    "username": "root",
    "password": "123456"
  }', now(), now(), 0);
COMMIT;

-- ----------------------------
-- Table structure for application_interface
-- ----------------------------
DROP TABLE IF EXISTS `application_interface`;
CREATE TABLE `application_interface`
(
    `id`                bigint(21) NOT NULL AUTO_INCREMENT comment '主键id',
    `system_id`         varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '系统标识',
    `interface_id`      varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '接口标识',
    `interface_name`    varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '接口名称',
    `interface_type`    varchar(32) COLLATE utf8_bin  DEFAULT NULL COMMENT '方法请求类型；DB_MYSQL、RPC_DUBBO等',
    `interface_version` varchar(16) COLLATE utf8_bin  DEFAULT NULL COMMENT '接口版本',
    `extension`         text(4000) COLLATE utf8_bin   DEFAULT null comment '扩展字段，建议存json格式',
    `create_time`       datetime                      DEFAULT NULL COMMENT '创建时间',
    `update_time`       datetime                      DEFAULT NULL COMMENT '更新时间',
    `is_deleted`        int(1)                        DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx` (`system_id`, `interface_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment '应用接口表';

-- ----------------------------
-- Records of application_interface
-- ----------------------------
BEGIN;
INSERT INTO simple_gateway.application_interface (id, system_id, interface_id, interface_name, interface_type,
                                                  interface_version,
                                                  create_time, update_time, is_deleted)
VALUES (1, 'simple-gateway-test-provider', 'com.simple.gateway.test.provider.rpc.IActivityBooth', '测试接口', 'RPC_DUBBO',
        '1.0.0',
        now(), now(), 0);

INSERT INTO simple_gateway.application_interface (id, system_id, interface_id, interface_name, interface_type,
                                                  interface_version,
                                                  extension, create_time, update_time, is_deleted)
VALUES (2, 'db-mysql-local-test', 'com.simple.gateway.db.mysql.TestDb', '测试数据库', 'DB_MYSQL', '1.0.0', '{
    "username": "root",
    "password": "970412@wcx.com"
  }', now(), now(), 0);
COMMIT;

-- ----------------------------
-- Table structure for application_interface_method
-- ----------------------------
DROP TABLE IF EXISTS `application_interface_method`;
CREATE TABLE `application_interface_method`
(
    `id`                bigint(21) NOT NULL AUTO_INCREMENT comment '主键id',
    `system_id`         varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '系统标识',
    `interface_id`      varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '接口标识',
    `method_id`         varchar(64) COLLATE utf8_bin  DEFAULT NULL COMMENT '方法标识',
    `method_name`       varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '方法名称',
    `parameter_type`    varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '参数类型；(RPC 限定单参数注册)；new String[]{"java.lang.String"}、new String[]{"com.simple.gateway.test.provider.rpc.dto.UserRequestDTO"}',
    `uri`               varchar(126) COLLATE utf8_bin DEFAULT NULL COMMENT '网关接口',
    `http_command_type` varchar(32) COLLATE utf8_bin  DEFAULT NULL COMMENT '接口类型；GET、POST、PUT、DELETE',
    `auth`              int(4)                        DEFAULT NULL COMMENT 'true = 1是、false = 0否',
    `extension`         text(4000) COLLATE utf8_bin   DEFAULT null comment '扩展字段，建议存json格式',
    `create_time`       datetime                      DEFAULT NULL COMMENT '创建时间',
    `update_time`       datetime                      DEFAULT NULL COMMENT '更新时间',
    `is_deleted`        int(1)                        DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx` (`system_id`, `interface_id`, `method_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment '应用方法表';

-- ----------------------------
-- Records of application_interface_method
-- ----------------------------
BEGIN;
INSERT INTO simple_gateway.application_interface_method (id, system_id, interface_id, method_id, method_name,
                                                         parameter_type, uri, http_command_type, auth, create_time,
                                                         update_time, is_deleted)
VALUES (1, 'simple-gateway-test-provider', 'com.simple.gateway.test.provider.rpc.IActivityBooth', 'sayHi', '测试查询方法',
        'java.lang.String', '/test/sayHi', 'GET', 0, now(), now(), 0);
INSERT INTO simple_gateway.application_interface_method (id, system_id, interface_id, method_id, method_name,
                                                         parameter_type, uri, http_command_type, auth, create_time,
                                                         update_time, is_deleted)
VALUES (2, 'simple-gateway-test-provider', 'com.simple.gateway.test.provider.rpc.IActivityBooth', 'insert', '测试插入方法',
        'com.simple.gateway.test.provider.rpc.dto.XReq', '/test/insert', 'POST', 1, now(), now(), 0);

INSERT INTO simple_gateway.application_interface_method (id, system_id, interface_id, method_id, method_name,
                                                         parameter_type, uri, http_command_type, auth, create_time,
                                                         update_time, is_deleted)
VALUES (3, 'db-mysql-local-test', 'com.simple.gateway.db.mysql.TestDb', 'select * from user_info where name = @name',
        '测试sql查询',
        '', '/test/mysql/get', 'GET', 1, now(), now(), 0);
COMMIT;


-- ----------------------------
-- Table structure for gateway_user
-- ----------------------------
DROP TABLE IF EXISTS `gateway_user`;
CREATE TABLE `gateway_user`
(
    `id`          bigint(21) NOT NULL AUTO_INCREMENT comment '主键id',
    `user_id`     varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '用户id',
    `user_name`   varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '用户名称（对应厂商名称）',
    `auth_type`   varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '授权类型，例如USER_TEMP_TOKEN、USER_AK',
    `ak`          varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '颁发的ak',
    `extension`   text(4000) COLLATE utf8_bin  DEFAULT null comment '扩展字段，建议存json格式',
    `create_time` datetime                     DEFAULT NULL COMMENT '创建时间',
    `update_time` datetime                     DEFAULT NULL COMMENT '更新时间',
    `is_deleted`  int(1)                       DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment '网关用户表';

-- ----------------------------
-- Table structure for gateway_user_interface_relation
-- ----------------------------
DROP TABLE IF EXISTS `gateway_user_interface_relation`;
CREATE TABLE `gateway_user_interface_relation`
(
    `id`                              bigint(21) NOT NULL AUTO_INCREMENT comment '主键id',
    `gateway_user_id`                 bigint(21)                  DEFAULT NULL COMMENT '网关用户id',
    `application_interface_method_id` bigint(21)                  DEFAULT NULL COMMENT '方法id',
    `extension`                       text(4000) COLLATE utf8_bin DEFAULT null comment '扩展字段，建议存json格式',
    `create_time`                     datetime                    DEFAULT NULL COMMENT '创建时间',
    `update_time`                     datetime                    DEFAULT NULL COMMENT '更新时间',
    `is_deleted`                      int(1)                      DEFAULT NULL comment '是否删除',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin comment '网关用户接口关联表';
